To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Chapter 28 Using Procedures, Functions, and Packages
Using functions, procedures, and packages can improve performance in several ways. These performance enhancements take the form of reduction in the amount of data that must be transmitted across the network and an increase in hits in the shared SQL cache.
Procedures and functions are subprograms made up of PL/SQL code that take a set of parameters given to them by the calling program and perform a set of actions. The only real difference between a procedure and a function is that a function can include a return value. Both functions and procedures can modify and return data passed to them as a parameter. Usually, procedures are used unless only one return value is needed.
Packages are sets of related procedures or functions compiled and stored together in the data dictionary. Packages allow you to group PL/SQL types, objects, and subprograms into a logical unit. If you link these logically related entities together, it can be easier to program and modify modules based on their function and relation. You enhance performance because the entire package is loaded into memory when it is first called, increasing the chance for a cache hit on a related function or object that is likely to be called soon.
Because a procedure, function, or package is stored within the library cache, it is available for immediate use by your applications. Because these objects are stored in an already-parsed form, performance is also improved.
Procedures, functions, and packages are used to call certain SQL statements that are used over and over again. Any set of SQL statements that you use frequently in your application can benefit from being made into a stored procedure or function.
This chapter reviews some information about the library cache and then describes how procedures, functions, and packages can be used to help improve overall performance.
Review of the Library Cache
As you know, the library cache contains the shared SQL and PL/SQL areas. By increasing the cache-hit rate in the library cache, you increase performance. This increase comes both from reducing the overhead needed to parse the SQL statements in the shared SQL area and from retrieving those statements from cache (reducing the need to retrieve those statements from disk).
A cache miss in the shared SQL area occurs either when a parse statement is called and the already-parsed statement does not exist in the shared SQL area or when an application tries to execute an SQL statement and the shared SQL area containing the parsed statement has been deallocated from the library cache.
Here is a review of the requirements necessary for an SQL statement to take advantage of the library cache. For an SQL statement to take advantage of SQL or PL/SQL statements that have already been parsed, the following criteria must be met:
- The text of the SQL statement must be identical to the SQL statement that has already been parsed. This includes whitespaces.
- References to schema objects in the SQL statements must resolve to the same object.
- Bind variables must match the same name and data type.
- The SQL statements must be optimized using the same approach; in the case of the cost-based approach, the same optimization goal.
You may think that these conditions make it difficult to take advantage of the shared SQL areas. But by reusing application code, you can quite easily meet these conditions. When writing applications, you should strive to use the same SQL statements to access the same data and ensure that these SQL statements can meet these criteria.
Use stored procedures and functions whenever possible to guarantee that the same shared PL/SQL area is used. Another advantage is that stored procedures are stored in a parsed formeliminating runtime parsing altogether.
Standardizing on naming conventions for bind variables and spacing conventions for SQL and PL/SQL statements can also increase the likelihood of reusing shared SQL statements.
|